package com.hc.core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


public class ExcelTool {
	Workbook wb = null;
	List<String[]> dataList = new ArrayList<String[]>(100);

	public static void main(String[] arg) throws FileNotFoundException {
		ExcelTool er = new ExcelTool();
		
		InputStream is = new FileInputStream(new File("F://拔尖计划信息模板1.xls"));
		List list = er.readExcelData(is);
		for (int i = 0; null != list && i < list.size(); i++) {
			String[] str = (String[]) list.get(i);
			for (int j = 0; null != str && j < str.length; j++) {
				System.out.print(str[j]);
			}
			System.out.println("");
		}
	}

	
	
	
	/**
	 * 读取Excel数据
	 * 
	 * @param is
	 * @return
	 */
	public List<String[]> readExcelData(InputStream is) {
		Workbook wb = null;
		List<String[]> dataList = new ArrayList<String[]>(100);
		//1.init workBookFactory
		try {
			wb = WorkbookFactory.create(is);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		//2.read zero sheetData
		int sheetIndex = 0;
		int columnNum = 0;
		Sheet sheet = wb.getSheetAt(sheetIndex);
		if (sheet.getRow(0) != null) {
			columnNum = sheet.getRow(1).getLastCellNum()
					- sheet.getRow(1).getFirstCellNum();
		}
		
		if (columnNum > 0) {
			int rowNum = 0;
			for (Row row : sheet) {
				String[] singleRow = new String[columnNum + 1];
				rowNum ++;
				singleRow[0] = String.valueOf(rowNum);
				int n = 1;
				for (int i = 0; i < columnNum; i++) {
					Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
					switch (cell.getCellType()) {
					case Cell.CELL_TYPE_BLANK:
						singleRow[n] = "";
						break;
					case Cell.CELL_TYPE_BOOLEAN:
						singleRow[n] = Boolean.toString(cell
								.getBooleanCellValue());
						break;
					// 数值
					case Cell.CELL_TYPE_NUMERIC:
						if (DateUtil.isCellDateFormatted(cell)) {
							singleRow[n] = String.valueOf(cell
									.getDateCellValue());
						} else {
							cell.setCellType(Cell.CELL_TYPE_STRING);
							String temp = cell.getStringCellValue();
							// 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
							if (temp.indexOf(".") > -1) {
								singleRow[n] = String.valueOf(new Double(temp))
										.trim();
							} else {
								singleRow[n] = temp.trim();
							}
						}
						break;
					case Cell.CELL_TYPE_STRING:
						singleRow[n] = cell.getStringCellValue().trim();
						break;
					case Cell.CELL_TYPE_ERROR:
						singleRow[n] = "";
						break;
					case Cell.CELL_TYPE_FORMULA:
						cell.setCellType(Cell.CELL_TYPE_STRING);
						singleRow[n] = cell.getStringCellValue();
						if (singleRow[n] != null) {
							singleRow[n] = singleRow[n].replaceAll("#N/A", "")
									.trim();
						}
						break;
					default:
						singleRow[n] = "";
						break;
					}
					n++;
				}
				
				if(rowNum == 2 || rowNum > 3){//第1行表头,第4行数据开始
					dataList.add(singleRow);
				}
			}
		}
		return dataList;
	}
	
	
	 /** 
     * 标题行样式 
     *  
     * @return 
     */  
    public HSSFCellStyle getTitleStyle(HSSFWorkbook workBook) {  
        HSSFCellStyle style = workBook.createCellStyle();  
        HSSFFont font = workBook.createFont();  
        font.setFontName("Arial");  
        font.setFontHeightInPoints((short) 12);// 设置字体大小  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中  
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上线居中  
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        style.setWrapText(true);  
        style.setFont(font);  
        return style;  
    }  
}